前面已經知道如何抓「臺灣證券交易所」的除權除息計算結果表 CSV 檔,接下來要處理資料,並存入 DB
需要考量的情境,與前一篇是一樣的 (描述越來越精簡 XD)
# app/features/twse/twt_49u/save_to_db.rb
module Twse::Twt49u
class SaveToDb
include Twse::Helpers
def execute
start_time = Time.current
puts "#{self.class}, start_time: #{start_time.to_s}"
latest_data_date = find_latest_data_date
return puts "#{self.class}, 已經是最新的資料" if latest_data_date == start_time.to_date
is_linux = `uname -a`[/Linux/].present?
file_paths = Dir["data/twse/TWT49U/*"]
file_paths.each do |file_path|
rows = decode_data(file_path, is_linux)
first_year, end_year = time_range(rows)
row_index, end_index = rows_range(rows)
all_rows, data_date_infos = filter_rows(rows, row_index, end_index)
next if not_process?(data_date_infos, latest_data_date)
filtered_stocks = filter_by_stocks(all_rows)
Stock.import(filtered_stocks) if filtered_stocks.present?
import_ex_stocks(all_rows)
end
puts "#{self.class}, done_time:#{Time.current}, #{(Time.current - start_time).to_s} sec"
rescue StandardError => e
puts "errors: #{e.inspect}, #{e.backtrace}"
end
private
def find_latest_data_date
ExStock.latest_data_date
end
def time_range(rows)
first_year, first_month, first_day, end_year, end_month, end_day = rows[0].scan(/\d+/)
[first_year, end_year].each_with_index do |year, index|
year = "20" + (year.to_i + 11).to_s[1..2]
if index.zero?
first_year = year
else
end_year =year
end
end
return [first_year, end_year]
end
def rows_range(rows)
row_index = nil
rows.each_with_index do |row, index|
if row.include?("資料日期")
row_index = index
break
end
end
end_index = nil
rows.each_with_index do |row, index|
if row.include?("公式")
end_index = index
break
end
end
return [row_index, end_index]
end
def filter_rows(rows, row_index, end_index)
all_rows = []
data_date_infos = []
rows[(row_index + 1)..(end_index - 1)].each do |row_string|
row_item = []
row_string.split('",').each { |row| row_item << row.gsub(/[=|,|"]/, '') }
year, month, day = row_item[0].scan(/\d+/)
year = "20" + (year.to_i + 11).to_s[1..2]
data_date = year + month + day
row_item[0] = data_date.to_date
all_rows << row_item
data_date_infos << row_item[0]
end
return [all_rows, data_date_infos]
end
def not_process?(data_date_infos, latest_data_date)
data_date_infos.uniq!
latest_data_date.present? && data_date_infos.max <= latest_data_date
end
def filter_by_stocks(all_rows)
stock_infos = []
all_rows.each { |rows| stock_infos << { code: rows[1], name: rows[2] } }
stocks = Stock.all.select(:code).index_by(&:code)
need_create_stocks = []
stock_infos.each do |stock_info|
stock = stocks[stock_info[:code]]
next if stock
next if need_create_stocks.any? { |item| item.code == stock_info[:code] }
need_create_stocks << Stock.new(code: stock_info[:code], name: stock_info[:name])
end
need_create_stocks
end
def import_ex_stocks(all_rows)
stocks = Stock.all.select(:code).index_by(&:code)
need_create_ex_stocks = []
all_rows.each do |row|
stock = stocks[row[1]]
need_create_ex_stocks << stock.exs.new(
data_date: row[0],
closing_price_before: row[3],
reference_price: row[4],
dr_value: row[5],
dividend_right: ExStock::DIVIDEND_RIGHT[row[6]],
limit_up: row[7],
limit_down: row[8],
opening_reference_price: row[9],
ex_dividend_reference_price: row[10],
reporting_day: row[12],
price_book: row[13],
eps: row[14],
)
end
ExStock.import(need_create_ex_stocks) if need_create_ex_stocks.present?
end
end
end
我猜看到這,已經不少人看不懂或沒在看了 (笑)
說明越寫越精簡,想說 code 已經直接貼出來了,直接看 code 比較快,若發現有更好的寫法,歡迎留言和我說~
鐵人賽文章連結:https://ithelp.ithome.com.tw/articles/10272913
medium 文章連結:https://link.medium.com/VfJfmvMuTjb
本文同步發布於 小菜的 Blog https://riverye.com/
備註:之後文章修改更新,以個人部落格為主